﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PPI.Client.Logic.Inspection;
using PPI.Client.Utils;
using PPI.Entities;
using PPI.Entities.Inspections;
using PPI.Entities.Lookup;
using PPI.Entities.Utility;
using PPI.Mobile.Logic;
using VMind.Global;

namespace PPI.Client.Logic.Utility
{
    public class LUtility_Asset_XMissionStructure
    {
        public static Int32 DuplicateByInspection(Int32 inspectionId)
        {
            return WebConsumer.Utility.WUtiilty_Asset_XMissionStructure.DuplicateByInspection(inspectionId);
        }

        public static Int32 DuplicateByVisit(Int32 visitId)
        {
            return WebConsumer.Utility.WUtiilty_Asset_XMissionStructure.AssociateWithVisit(visitId, Int32.MinValue);
        }

        public static Int32 AssociateWithVisit(Int32 visitId, Int32 utilityId)
        {
            return WebConsumer.Utility.WUtiilty_Asset_XMissionStructure.AssociateWithVisit(visitId, utilityId);
        }

        public static Int32 AssociateWithInspection(Int32 inspectionId, Int32 utilityId)
        {
            return WebConsumer.Utility.WUtiilty_Asset_XMissionStructure.AssociateWithInspection(inspectionId, utilityId);
        }

        public static void AssociateWithInspectionMobile(Int32 inspectionid, Int32 utilityId,string connectionString)
        {
            StringBuilder _sql = new StringBuilder();
            _sql.AppendLine("Update tb_Vendor_InspVisit_XMissionStructures SET Utility_Asset_XMisssionStructureFk = @Utility_Asset_XMisssionStructureFk");
            _sql.AppendLine(" Where Vendor_InspVisit_XMissionStructureId = @inspectionid");

            List<SqlParameter> _listParameters = new List<SqlParameter>();
            _listParameters.Add(DBHelper.CreateIntParameter("@Utility_Asset_XMisssionStructureFk", ParameterDirection.Input, false, utilityId));
            _listParameters.Add(DBHelper.CreateIntParameter("@inspectionid", ParameterDirection.Input, false, inspectionid));

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_sql.ToString(), connectionString, _listParameters.ToArray());

        }

        public static void AssociateWithVisitMobile(Int32 visitId, Int32 utilityId, string connectionString)
        {
            StringBuilder _sql = new StringBuilder();
            _sql.AppendLine("Update tb_Vendor_Visits SET AssetFk = @AssetFk");
            _sql.AppendLine(" Where VisitId = @visitId");

            List<SqlParameter> _listParameters = new List<SqlParameter>();
            _listParameters.Add(DBHelper.CreateIntParameter("@AssetFk", ParameterDirection.Input, false, utilityId));
            _listParameters.Add(DBHelper.CreateIntParameter("@visitId", ParameterDirection.Input, false, visitId));

            LMobileDatabase.ExecuteSingleQueryWhithParameters(_sql.ToString(), connectionString, _listParameters.ToArray());

        }

        public static void Save(EUtility_Asset_XMissionStructure asset, string connectionString)
        {
            StringBuilder sql = new StringBuilder();

            #region Query
            sql.AppendLine("Insert into tb_Utility_Asset_XMissionStructures ");
            sql.AppendLine(" Utility_Asset_XMissionStructureId, ");
            sql.AppendLine(" UtilityFK, ");
            sql.AppendLine(" Latitude, ");
            sql.AppendLine(" Longitude, ");
            sql.AppendLine(" Altitude, ");
            sql.AppendLine(" SatelliteCount, ");
            sql.AppendLine(" SatelliteTime, ");
            sql.AppendLine(" SolutionAvailable, ");
            sql.AppendLine(" XMissionStructures_TopTypeFk, ");
            sql.AppendLine(" Utility_ContactFK, ");
            sql.AppendLine(" Date, ");
            sql.AppendLine(" Utility_ProjectFK ");
            sql.AppendLine(")Values(");
            sql.AppendLine(" @Utility_Asset_XMissionStructureId, ");
            sql.AppendLine(" @UtilityFK, ");
            sql.AppendLine(" @Latitude, ");
            sql.AppendLine(" @Longitude, ");
            sql.AppendLine(" @Altitude, ");
            sql.AppendLine(" @SatelliteCount, ");
            sql.AppendLine(" @SatelliteTime, ");
            sql.AppendLine(" @SolutionAvailable, ");
            sql.AppendLine(" @XMissionStructures_TopTypeFk, ");
            sql.AppendLine(" @Utility_ContactFK, ");
            sql.AppendLine(" @Date, ");
            sql.AppendLine(" @Utility_ProjectFK ");
            sql.AppendLine(")");
            #endregion

            List<SqlParameter> listParameters = new List<SqlParameter>();
            DBHelper.CreateIntParameter("@Utility_Asset_XMissionStructureId", ParameterDirection.Input, false, asset.Id);
            DBHelper.CreateIntParameter("@UtilityFK", ParameterDirection.Input, true, DBNull.Value);
            DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, asset.Latitude);
            DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, asset.Longitude);
            DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, false, asset.Altitude);
            DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, false, asset.SatelliteCount);
            DBHelper.CreateDateTimeParameter("@SatelliteTime", ParameterDirection.Input, false, asset.SatelliteTime);
            DBHelper.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, asset.SatelliteTime);
            if (asset.TopType.XMissionStructures_TopTypeId != Int32.MinValue)
                DBHelper.CreateIntParameter("@XMissionStructures_TopTypeFk", ParameterDirection.Input, false, asset.TopType.XMissionStructures_TopTypeId);
            else
                DBHelper.CreateIntParameter("@XMissionStructures_TopTypeFk", ParameterDirection.Input, true, DBNull.Value);
            DBHelper.CreateIntParameter("@Utility_ContactFK", ParameterDirection.Input, true, DBNull.Value);
            DBHelper.CreateDateTimeParameter("@Date", ParameterDirection.Input, false, asset.Date);
            DBHelper.CreateIntParameter("@Utility_ProjectFK", ParameterDirection.Input, true, 1);

            try
            {
                LMobileDatabase.ExecuteSingleQueryWhithParameters(sql.ToString(), connectionString, listParameters.ToArray());
            }
            catch(Exception ex)
            {
                RegisterError.SaveError(ex);
            }
        }

        public static List<KeyValuePair<Int32, string>> SelectNearLatitudeLongitude(double latitude, double longitude)
        {
            List<KeyValuePair<Int32, string>> returnAssets = new List<KeyValuePair<int, string>>();

            DataSet dsNearLatitudeLongitude =WebConsumer.Utility.WUtiilty_Asset_XMissionStructure.SelectNearLatitudeLongitude(latitude, longitude);

            if(dsNearLatitudeLongitude != null)
            {
                DataTable dtNearLatitudeLongitude = dsNearLatitudeLongitude.Tables[0];

                foreach (DataRow dr in dtNearLatitudeLongitude.Rows)
                {
                    returnAssets.Add(new KeyValuePair<int, string>(ConvertHelper.ToInt32(dr["Utility_Asset_XMissionStructureId"]), ConvertHelper.ToInt32(dr["Utility_Asset_XMissionStructureId"])+"-->"+ ConvertHelper.Todouble(dr["distance"]) +" mts."));
                }
            }
            return returnAssets;
        }

        public static void AutolinkInspection(double maxdistanceInMts)
        {
            WebConsumer.Utility.WUtiilty_Asset_XMissionStructure.AutolinkInspection(maxdistanceInMts);
        }

        public static void AutolinkVendor(double maxdistanceInMts)
        {
            WebConsumer.Utility.WUtiilty_Asset_XMissionStructure.AutolinkVendor(maxdistanceInMts);
        }

        #region Query Mobile

        public static List<EUtility_Asset_XMissionStructure> SelectAllMobile(string connectionString)
        {
            return SelectAllMobile(Int32.MinValue, connectionString);
        }

        public static List<EUtility_Asset_XMissionStructure> SelectAllMobile(Int32 id,string connectionString)
        {
            List<EUtility_Asset_XMissionStructure> listAssets = new List<EUtility_Asset_XMissionStructure>();
            StringBuilder _sql = new StringBuilder();

            #region Query
            _sql.AppendLine("Select * from tb_Utility_Asset_XMissionStructures where (@Utility_Asset_XMissionStructureId is null OR Utility_Asset_XMissionStructureId = @Utility_Asset_XMissionStructureId)");
            #endregion

            List<SqlParameter> listParameters = new List<SqlParameter>();
            if(id == Int32.MinValue)
                listParameters.Add(DBHelper.CreateIntParameter("@Utility_Asset_XMissionStructureId",ParameterDirection.Input,true,DBNull.Value));
            else
                listParameters.Add(DBHelper.CreateIntParameter("@Utility_Asset_XMissionStructureId", ParameterDirection.Input, false, id));

            DataSet dsAsset = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString,listParameters.ToArray());

            if (dsAsset != null)
            {
                DataTable dtAsset = dsAsset.Tables[0];
                listAssets = ParseDatatableAsset(dtAsset);
            }
            return listAssets;
        }

        private static List<EUtility_Asset_XMissionStructure> ParseDatatableAsset(DataTable dtAssets)
        {
            List<EUtility_Asset_XMissionStructure> listAssets = new List<EUtility_Asset_XMissionStructure>();

            foreach (DataRow dr in dtAssets.Rows)
            {
                EUtility_Asset_XMissionStructure tmpAsset = new EUtility_Asset_XMissionStructure();
                tmpAsset.Contact = new EUtility_Contact(ConvertHelper.ToInt32(dr["Utility_ContactFK"]));
                tmpAsset.Date = ConvertHelper.ToDateTime(dr["Date"]);
                tmpAsset.Handheld = new EHandheld(ConvertHelper.ToInt32(dr["Handheld"]), "");
                tmpAsset.Id = ConvertHelper.ToInt32(dr["Utility_Asset_XMissionStructureId"]);
                tmpAsset.ServerID = tmpAsset.Id;
                tmpAsset.Latitude = ConvertHelper.Todouble(dr["Latitude"]);
                tmpAsset.Longitude = ConvertHelper.Todouble(dr["Longitude"]);
                tmpAsset.Altitude = ConvertHelper.Todouble(dr["Altitude"]);
                tmpAsset.Project = new EUtility_Project(ConvertHelper.ToInt32(dr["Utility_ProjectFK"]));
                tmpAsset.SatelliteCount = ConvertHelper.ToInt32(dr["SatelliteCount"]);
                tmpAsset.SatelliteTime = ConvertHelper.ToDateTime(dr["SatelliteTime"]);
                tmpAsset.TopType = new EXMissionStructures_TopType(ConvertHelper.ToInt32(dr["XMissionStructures_TopTypeFk"]));
                tmpAsset.Utility = new EUtility(ConvertHelper.ToInt32(dr["UtilityFK"]));
                listAssets.Add(tmpAsset);
            }
            return listAssets;
        }

        public static List<EUtility_Asset_XMissionStructure> SelectAllWithInspectionGps(string connectionString)
        {
            return SelectAllWithInspectionGps(Int32.MinValue, connectionString);
        }

        public static List<EUtility_Asset_XMissionStructure> SelectAllWithInspectionGps(Int32 assetId,string connectionString)
        {
            try
            {
                List<EUtility_Asset_XMissionStructure> listAssets = SelectAllMobile(assetId, connectionString);
                List<EVendor_Visit_XMissionStructure> listVisitsGps = LVendor_Visit_XMissionStructure.GetMobileProjectByAsset(assetId, connectionString);
                List<EInspectionStructure> listVisitsInspection = LInspectionStructure.LoadProjectStructuresByAsset(assetId, connectionString);

                foreach (EUtility_Asset_XMissionStructure asset in listAssets)
                {
                    foreach (EVendor_Visit_XMissionStructure gps in listVisitsGps)
                    {
                        if (asset.Id == gps.UtilityAsset.Id)
                            asset.VisitGps.Add(gps);
                    }

                    foreach (EInspectionStructure inspection in listVisitsInspection)
                    {
                        if (asset.Id == inspection.UtilityAsset.Id)
                            asset.VisitInspections.Add(inspection);
                    }
                }
                return listAssets;
            }
            catch (Exception ex)
            {
                RegisterError.SaveError(ex);
                return new List<EUtility_Asset_XMissionStructure>();
            }
        }

        #endregion
    }
}
